/**
 * 
 */
package com.orange.modules.dao;

import java.sql.SQLException;
import java.util.Map;

import java.sql.SQLException;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.entity.expert.AuthGroup;

/**
 * @author gaogs
 *
 *         2016-11-16
 */
@Repository
public class AuthGroupRepository {

	@Resource(name = "dbHelper")
	private DbHelper dbHelper;

	@Autowired
	private AuthGroupDao authExportGroupDao;
	@Autowired
	private AuthExportGroupMapDao authExportGroupMapDao;

	@Autowired
	private AuthGroupDao authGroupDao ;
	
	/**
	 * 保存专家组信息
	 * 
	 * @param a
	 */
	public void saveAuthExportGroupInfo(AuthGroup a) {
		authGroupDao.save(a);
	}

	/**
	 * 根据专家组id查询组信息
	 * @param pid
	 * @return
	 */
	public AuthGroup queryAuthGroupInfoById(String pid){
		AuthGroup aa=authGroupDao.findOne(pid);
		return aa;
	}
	
	/**
	 * 查询专家组(根据id)
	 * @throws SQLException 
	 */
	public AuthGroup queryAuthExportGroupInfo(String pid) throws SQLException{
		String sql = "SELECT a.p_id as pid,a.s_type as type,a.s_name as name,a.s_state as state,"
				+"a.s_school_id as schoolId,a.s_update_time as updateTime,a.s_create_time as createTime"
				+" FROM t_auth_group a WHERE 1 = 1";
		if(pid !=null && StringUtils.isNotBlank(pid)){
			sql+="AND a.p_id = '"+pid+"'";
		}
		AuthGroup ag = dbHelper.getBean(sql, AuthGroup.class);
		return ag;
	}
	
	
	/**
	 * 删除组
	 * 
	 * @param pid
	 * @throws SQLException
	 */
	public void deleteAuhExportGroupInfo(String pid) throws SQLException {
		authExportGroupDao.delete(pid);
	}

	/**
	 * 根据学校id查询专家组信息
	 */
	public AuthGroup queryAuthGroupBySchoolId(String schoolId) throws SQLException {
		String sql = "select top 1  a.p_id as pid,a.s_type as type,a.s_name as name,"
				+ "a.s_state as state,a.s_school_id as schoolid,"
				+ "a.s_update_time as updatetime,a.s_create_time as create_time "
				+ " from t_auth_group as a where a.s_school_id = ?";
		AuthGroup ls = dbHelper.getBean(sql, AuthGroup.class,schoolId);
		return ls;
	}


	/**
	 * 分页
	 * 
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthGroup> queryAuthGroupPage(Map<String, String> map) throws SQLException {
		StringBuilder sql = new StringBuilder(
				" SELECT TOP 1000 a.p_id as pid,a.s_type as type,a.s_name as name," +
				" a.s_state as state,a.s_school_id as schoolId,a.s_create_time as create_time ," +
				" b.s_school_name as schoolName ,b.s_major as major" +
				" FROM  t_auth_group as a " +
				" LEFT JOIN t_auth_school as b ON a.s_school_id=b.p_id  " +
				" WHERE a.s_type='1' AND a.s_state !='2'" +
				"");
		if (StringUtils.isNotBlank(map.get("schoolName"))) {
			sql.append(" AND b.s_school_name like '%" + map.get("schoolName")+ "%'");
		}
		if (StringUtils.isNotBlank(map.get("name"))) {
			sql.append(" AND a.s_name like '%" + map.get("name")+ "%'");
		}
		if (StringUtils.isNotBlank(map.get("major"))) {
			sql.append(" AND b.s_major =" + map.get("major"));
		}
		sql.append(" ORDER BY a.s_create_time DESC ");
		Page<AuthGroup> authGroup = dbHelper.getPage(sql.toString(),AuthGroup.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return authGroup;
	}
	
	/**
	 * 分页查询材料评审专家组
	 * 
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthGroup> queryAuthGroupPage_material(Map<String, String> map) throws SQLException {
		StringBuilder sql = new StringBuilder(
				" SELECT TOP 1000 a.p_id as pid,a.s_type as type,a.s_name as name," +
				" a.s_state as state,a.s_school_id as schoolId,a.s_create_time as create_time " +
				" FROM  t_auth_group as a " +
				" WHERE a.s_type='0' " +
				"");
		if (StringUtils.isNotBlank(map.get("name"))) {
			sql.append(" AND a.s_name like '%" + map.get("name")+ "%'");
		}
		sql.append(" ORDER BY a.s_create_time DESC ");
		Page<AuthGroup> authGroup = dbHelper.getPage(sql.toString(),AuthGroup.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return authGroup;
	}

}
